﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CaseReivew.Model;
using CaseReview.DBUtility;

namespace CaseReview.SQLServerDAL {
    public class ReviewResult
    {
        private const string SQL_INSERT_RESULT = @"INSERT INTO dbo.ReviewResults (CaseId,Owner,Reviewer,date,Cac1,Cac2,Cac3,Cac4,Bac1,
                                                   Bac2,Bac3,Nce1,Nce2,Nce3,Nce4,Nce5,Nce6,Nce7,Nce8,Nce9,Nce10,Cac1C,Cac2C,Cac3C,Cac4C,
                                                   Bac1C,Bac2C,Bac3C,Nce1C,Nce2C,Nce3C,Nce4C,Nce5C,Nce6C,Nce7C,Nce8C,Nce9C,Nce10C,Osum,Osug,status)
                                                   VALUES(@CaseId,@Owner,@Reviewer,@date,@Cac1,@Cac2,@Cac3,@Cac4,@Bac1,@Bac2,@Bac3,@Nce1,@Nce2,@Nce3,
                                                    @Nce4,@Nce5,@Nce6,@Nce7,@Nce8,@Nce9,@Nce10,@Cac1C,@Cac2C,@Cac3C,@Cac4C,@Bac1C,@Bac2C,@Bac3C,
                                                   @Nce1C,@Nce2C,@Nce3C,@Nce4C, @Nce5C,@Nce6C,@Nce7C,@Nce8C,@Nce9C,@Nce10C,@Osum,@Osug,@Status)";
        private const string SQL_SELECT_TOTAL_CASE_BY_STATUS = @"SELECT CaseId,Owner,Reviewer,date,Cac1,Cac2,Cac3,Cac4,Bac1,Bac2,Bac3,Nce1,Nce2,Nce3,Nce4,
                                                    Nce5,Nce6,Nce7,Nce8,Nce9,Nce10,Cac1C,Cac2C,Cac3C,Cac4C,Bac1C,Bac2C,Bac3C,Nce1C,Nce2C,Nce3C,Nce4C,
                                                    Nce5C,Nce6C,Nce7C,Nce8C,Nce9C,Nce10C,Osum,Osug,status FROM dbo.ReviewResults WHERE status=@Status";
        private const string SQL_UPDATE_REVIEW_RESULT = @"Update dbo.ReviewResults SET CaseId = @CaseId,Owner = @Owner,Reviewer = @Reviewer,date = @date,
                                                    Cac1 = @Cac1,Cac1C = @Cac1C,Cac2 = @Cac2,Cac2C=@Cac2C,Cac3=@Cac3,Cac3C=@Cac3C,Cac4=@Cac4,Cac4C=@Cac4C,Bac1=@Bac1,Bac1C=@Bac1C,
                                                    Bac2=@Bac2,Bac2C=@Bac2C,Bac3=@Bac3,Bac3C=@Bac3C,Nce1=@Nce1,Nce1C=@Nce1C,Nce2=@Nce2,Nce2C=Nce2C,Nce3=@Nce3,
                                                    Nce3C=@Nce3C,Nce4=@Nce4,Nce4C=@Nce4C,Nce5=@Nce5,Nce5C=@Nce5C,Nce6=@Nce6,Nce6C=@Nce6C,Nce7=@Nce7,Nce7C=@Nce7C,
                                                    Nce8=@Nce8,Nce8C=@Nce8C,Nce9=@Nce9,Nce9C=@Nce9C,Nce10=@Nce10,Nce10C=@Nce10C,OSum=@Osum,OSug=@Osug,status=@Status
                                                     WHERE CaseId = @OldCaseId";
        private const string SQL_SELECT_REVIEWRESULT_BY_ID = @"SELECT CaseId,Owner,Reviewer,date,Cac1,Cac2,Cac3,Cac4,Bac1,Bac2,Bac3,Nce1,Nce2,Nce3,Nce4,
                                                    Nce5,Nce6,Nce7,Nce8,Nce9,Nce10,Cac1C,Cac2C,Cac3C,Cac4C,Bac1C,Bac2C,Bac3C,Nce1C,Nce2C,Nce3C,Nce4C,
                                                    Nce5C,Nce6C,Nce7C,Nce8C,Nce9C,Nce10C,Osum,Osug,status FROM dbo.ReviewResults WHERE CaseId = @CaseId";
        private const string PARM_CASE_ID = "@CaseId";
        private const string PARM_OWNER = "@Owner";
        private const string PARM_REVIEWER = "@Reviewer";
        private const string PARM_DATE = "@date";
        private const string PARM_CAC1 = "@Cac1";
        private const string PARM_CAC2 = "@Cac2";
        private const string PARM_CAC3 = "@Cac3";
        private const string PARM_CAC4 = "@Cac4";
        private const string PARM_BAC1 = "@Bac1";
        private const string PARM_BAC2 = "@Bac2";
        private const string PARM_BAC3 = "@Bac3";
        private const string PARM_NCE1 = "@Nce1";
        private const string PARM_NCE2 = "@Nce2";
        private const string PARM_NCE3 = "@Nce3";
        private const string PARM_NCE4 = "@Nce4";
        private const string PARM_NCE5 = "@Nce5";
        private const string PARM_NCE6 = "@Nce6";
        private const string PARM_NCE7 = "@Nce7";
        private const string PARM_NCE8 = "@Nce8";
        private const string PARM_NCE9 = "@Nce9";
        private const string PARM_NCE10 = "@Nce10";
        private const string PARM_CAC1C = "@Cac1C";
        private const string PARM_CAC2C = "@Cac2C";
        private const string PARM_CAC3C = "@Cac3C";
        private const string PARM_CAC4C = "@Cac4C";
        private const string PARM_BAC1C = "@Bac1C";
        private const string PARM_BAC2C = "@Bac2C";
        private const string PARM_BAC3C = "@Bac3C";
        private const string PARM_NCE1C = "@Nce1C";
        private const string PARM_NCE2C = "@Nce2C";
        private const string PARM_NCE3C = "@Nce3C";
        private const string PARM_NCE4C = "@Nce4C";
        private const string PARM_NCE5C = "@Nce5C";
        private const string PARM_NCE6C = "@Nce6C";
        private const string PARM_NCE7C = "@Nce7C";
        private const string PARM_NCE8C = "@Nce8C";
        private const string PARM_NCE9C = "@Nce9C";
        private const string PARM_NCE10C = "@Nce10C";
        private const string PARM_OSUM = "@Osum";
        private const string PARM_OSUG = "@Osug";
        private const string PARM_STATUS = "@Status";
        private const string PARM_OLD_CASE_ID = @"OldCaseId";

        public void Insert(ReviewResultInfo result)
        {
            SqlParameter[] sqlParameters = GetResultsParameters();
            SqlCommand sqlCommand = new SqlCommand();
            sqlParameters[0].Value = result.CaseId;
            sqlParameters[1].Value = result.Owner;
            sqlParameters[2].Value = result.Reviewer;
            sqlParameters[3].Value = result.Date;
            sqlParameters[4].Value = result.Cac1;
            sqlParameters[5].Value = result.Cac2;
            sqlParameters[6].Value = result.Cac3;
            sqlParameters[7].Value = result.Cac4;
            sqlParameters[8].Value = result.Bac1;
            sqlParameters[9].Value = result.Bac2;
            sqlParameters[10].Value = result.Bac3;
            sqlParameters[11].Value = result.Nce1;
            sqlParameters[12].Value = result.Nce2;
            sqlParameters[13].Value = result.Nce3;
            sqlParameters[14].Value = result.Nce4;
            sqlParameters[15].Value = result.Nce5;
            sqlParameters[16].Value = result.Nce6;
            sqlParameters[17].Value = result.Nce7;
            sqlParameters[18].Value = result.Nce8;
            sqlParameters[19].Value = result.Nce9;
            sqlParameters[20].Value = result.Nce10;
            sqlParameters[21].Value = result.Cac1C;
            sqlParameters[22].Value = result.Cac2C;
            sqlParameters[23].Value = result.Cac3C;
            sqlParameters[24].Value = result.Cac4C;
            sqlParameters[25].Value = result.Bac1C;
            sqlParameters[26].Value = result.Bac2C;
            sqlParameters[27].Value = result.Bac3C;
            sqlParameters[28].Value = result.Nce1C;
            sqlParameters[29].Value = result.Nce2C;
            sqlParameters[30].Value = result.Nce3C;
            sqlParameters[31].Value = result.Nce4C;
            sqlParameters[32].Value = result.Nce5C;
            sqlParameters[33].Value = result.Nce6C;
            sqlParameters[34].Value = result.Nce7C;
            sqlParameters[35].Value = result.Nce8C;
            sqlParameters[36].Value = result.Nce9C;
            sqlParameters[37].Value = result.Nce10C;
            sqlParameters[38].Value = result.Osum;
            sqlParameters[39].Value = result.Osug;
            sqlParameters[40].Value = result.Status;
            SqlHelper.executeNonQuery(SQL_INSERT_RESULT, sqlParameters);
        }

        public void UpdateResult(ReviewResultInfo result,string oldCaseId) {
            SqlParameter[] sqlParameters = new SqlParameter[]
                                               {
                                                  new SqlParameter(PARM_CASE_ID,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_OWNER,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_REVIEWER,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_DATE,SqlDbType.DateTime,12),
                                                  new SqlParameter(PARM_CAC1,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC2,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC3,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC4,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_BAC1,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_BAC2,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_BAC3,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE1,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE2,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE3,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE4,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE5,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE6,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE7,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE8,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE9,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE10,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC1C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_CAC2C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_CAC3C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_CAC4C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_BAC1C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_BAC2C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_BAC3C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE1C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE2C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE3C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE4C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE5C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE6C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE7C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE8C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE9C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE10C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_OSUM,SqlDbType.Text),
                                                  new SqlParameter(PARM_OSUG,SqlDbType.Text),
                                                  new SqlParameter(PARM_STATUS,SqlDbType.Int),
                                                  new SqlParameter(PARM_OLD_CASE_ID,SqlDbType.VarChar,50), 
                                               };

            sqlParameters[0].Value = result.CaseId;
            sqlParameters[1].Value = result.Owner;
            sqlParameters[2].Value = result.Reviewer;
            sqlParameters[3].Value = result.Date;
            sqlParameters[4].Value = result.Cac1;
            sqlParameters[5].Value = result.Cac2;
            sqlParameters[6].Value = result.Cac3;
            sqlParameters[7].Value = result.Cac4;
            sqlParameters[8].Value = result.Bac1;
            sqlParameters[9].Value = result.Bac2;
            sqlParameters[10].Value = result.Bac3;
            sqlParameters[11].Value = result.Nce1;
            sqlParameters[12].Value = result.Nce2;
            sqlParameters[13].Value = result.Nce3;
            sqlParameters[14].Value = result.Nce4;
            sqlParameters[15].Value = result.Nce5;
            sqlParameters[16].Value = result.Nce6;
            sqlParameters[17].Value = result.Nce7;
            sqlParameters[18].Value = result.Nce8;
            sqlParameters[19].Value = result.Nce9;
            sqlParameters[20].Value = result.Nce10;
            sqlParameters[21].Value = result.Cac1C;
            sqlParameters[22].Value = result.Cac2C;
            sqlParameters[23].Value = result.Cac3C;
            sqlParameters[24].Value = result.Cac4C;
            sqlParameters[25].Value = result.Bac1C;
            sqlParameters[26].Value = result.Bac2C;
            sqlParameters[27].Value = result.Bac3C;
            sqlParameters[28].Value = result.Nce1C;
            sqlParameters[29].Value = result.Nce2C;
            sqlParameters[30].Value = result.Nce3C;
            sqlParameters[31].Value = result.Nce4C;
            sqlParameters[32].Value = result.Nce5C;
            sqlParameters[33].Value = result.Nce6C;
            sqlParameters[34].Value = result.Nce7C;
            sqlParameters[35].Value = result.Nce8C;
            sqlParameters[36].Value = result.Nce9C;
            sqlParameters[37].Value = result.Nce10C;
            sqlParameters[38].Value = result.Osum;
            sqlParameters[39].Value = result.Osug;
            sqlParameters[40].Value = result.Status;
            sqlParameters[41].Value = oldCaseId;
            SqlHelper.executeNonQuery(SQL_UPDATE_REVIEW_RESULT, sqlParameters);
        }

        private static SqlParameter[] GetResultsParameters()
        {
            SqlParameter[] sqlParameters = new SqlParameter[]
                                               {
                                                  new SqlParameter(PARM_CASE_ID,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_OWNER,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_REVIEWER,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_DATE,SqlDbType.DateTime,12),
                                                  new SqlParameter(PARM_CAC1,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC2,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC3,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC4,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_BAC1,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_BAC2,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_BAC3,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE1,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE2,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE3,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE4,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE5,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE6,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE7,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE8,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE9,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_NCE10,SqlDbType.VarChar,50),
                                                  new SqlParameter(PARM_CAC1C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_CAC2C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_CAC3C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_CAC4C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_BAC1C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_BAC2C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_BAC3C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE1C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE2C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE3C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE4C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE5C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE6C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE7C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE8C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE9C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_NCE10C,SqlDbType.VarChar,500),
                                                  new SqlParameter(PARM_OSUM,SqlDbType.Text),
                                                  new SqlParameter(PARM_OSUG,SqlDbType.Text),
                                                  new SqlParameter(PARM_STATUS,SqlDbType.Int),
                                               };
            return sqlParameters;
        }

        public IList<ReviewResultInfo> GetTotalResultsByStatus(int status) {
            IList<ReviewResultInfo> reviewResultInfos = new List<ReviewResultInfo>();
            SqlParameter[] sqlParameters = {new SqlParameter((PARM_STATUS), SqlDbType.Int)};
            sqlParameters[0].Value = status;
            using (SqlDataReader sdr = SqlHelper.executeReader(SQL_SELECT_TOTAL_CASE_BY_STATUS,sqlParameters)) {
                while (sdr.Read()) {
                    reviewResultInfos.Add(new ReviewResultInfo(sdr.GetString(0), sdr.GetString(1), sdr.GetString(2),
                       sdr.GetDateTime(3), sdr.GetString(4), sdr.GetString(5), sdr.GetString(6), sdr.GetString(7), sdr.GetString(8), sdr.GetString(9),
                       sdr.GetString(10), sdr.GetString(11), sdr.GetString(12), sdr.GetString(13), sdr.GetString(14),
                       sdr.GetString(15), sdr.GetString(16), sdr.GetString(17), sdr.GetString(18), sdr.GetString(19),
                       sdr.GetString(20), sdr.GetString(21), sdr.GetString(22), sdr.GetString(23), sdr.GetString(24),
                       sdr.GetString(25), sdr.GetString(26), sdr.GetString(27), sdr.GetString(28), sdr.GetString(29),
                       sdr.GetString(30), sdr.GetString(31), sdr.GetString(32), sdr.GetString(33), sdr.GetString(34),
                       sdr.GetString(35), sdr.GetString(36), sdr.GetString(37), sdr.GetString(38), sdr.GetString(39),
                       sdr.GetInt32(40)));
                }
            }
            return reviewResultInfos;
        }


        public ReviewResultInfo GetReviewResultByCaseId(string caseId) {
            ReviewResultInfo resultInfo = new ReviewResultInfo();
            SqlParameter[] sqlParameters = { new SqlParameter((PARM_CASE_ID), SqlDbType.VarChar) };
            sqlParameters[0].Value = caseId;
            using (SqlDataReader sdr = SqlHelper.executeReader(SQL_SELECT_REVIEWRESULT_BY_ID, sqlParameters)) {
                while (sdr.Read())
                {

                    resultInfo = new ReviewResultInfo(sdr.GetString(0), sdr.GetString(1), sdr.GetString(2),
                                         sdr.GetDateTime(3), sdr.GetString(4), sdr.GetString(5), sdr.GetString(6),
                                         sdr.GetString(7),
                                         sdr.GetString(8), sdr.GetString(9), sdr.GetString(10), sdr.GetString(11),
                                         sdr.GetString(12),
                                         sdr.GetString(13), sdr.GetString(14), sdr.GetString(15), sdr.GetString(16),
                                         sdr.GetString(17),
                                         sdr.GetString(18), sdr.GetString(19), sdr.GetString(20), sdr.GetString(21),
                                         sdr.GetString(22),
                                         sdr.GetString(23), sdr.GetString(24), sdr.GetString(25), sdr.GetString(26),
                                         sdr.GetString(27),
                                         sdr.GetString(28), sdr.GetString(29), sdr.GetString(30), sdr.GetString(31),
                                         sdr.GetString(32),
                                         sdr.GetString(33), sdr.GetString(34), sdr.GetString(35), sdr.GetString(36),
                                         sdr.GetString(37), sdr.GetString(38), sdr.GetString(39),
                                         sdr.GetInt32(40));
                }
            }
            return resultInfo;
        }
    }
}
